﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DataModels.Common;
using DataModels.Enumerations;
using DataModels.Models.DHIS;
using DataModels.Models.Context;
using DataModels;

namespace GenericTools
{
    public partial class Utilities
    {
        #region Missing Reports
//        public static DataTable GetMissingFacilityReport(string facilityCode, int report,
//            int year, int period, int supportingPartnerId, DbCommand command)
//        {
//            command.CommandType = CommandType.Text;
//            command.CommandText = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name], 
//                @Year AS Year, @Period AS Period FROM Facilities INNER JOIN ExpectedReports ON 
//                Facilities.FacCode = ExpectedReports.FacCode WHERE (Facilities.FacCode NOT IN
//                (SELECT FacCode FROM ReceivedSMS WHERE (RepID = @RepID) AND (Period = @Period) AND (Year = @Year))) 
//                AND (Facilities.FacCode = @FacCode) AND (ExpectedReports.ReportID = @RepID) 
//                AND (CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS datetime) 
//                >= ExpectedReports.ReportingStartDate) AND (Facilities.Activated = 1) 
//                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
//            command.Parameters.Clear();
//            DbParameter param = command.CreateParameter();
//            param.ParameterName = "@RepID";
//            param.DbType = DbType.Int32;
//            param.Value = report;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@Period";
//            param.DbType = DbType.Int32;
//            param.Value = period;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@Year";
//            param.DbType = DbType.Int32;
//            param.Value = year;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@FacCode";
//            param.DbType = DbType.String;
//            param.Value = facilityCode;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@SupportingPartnerId";
//            param.DbType = DbType.Int32;
//            param.Value = supportingPartnerId;
//            command.Parameters.Add(param);

//            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
//            return dataTable;
//        }

//        private static DataTable GetSubmittedFacilityReport(string facilityCode, int report,
//            int year, int period, int supportingPartnerId, DbCommand command)
//        {
//            command.CommandType = CommandType.Text;
//            command.CommandText = @"SELECT FacCode AS [Facility Code], FacName AS [Facility Name], 
//                @Year AS [Year], @Period AS Period 
//                FROM Facilities WHERE (FacCode IN (SELECT FacCode FROM ReceivedSMS 
//                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) 
//                AND (FacCode = @FacCode) AND (Facilities.Activated = 1)  
//                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
//            command.Parameters.Clear();
//            DbParameter param = command.CreateParameter();
//            param.ParameterName = "@RepID";
//            param.DbType = DbType.Int32;
//            param.Value = report;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@Period";
//            param.DbType = DbType.Int32;
//            param.Value = period;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@Year";
//            param.DbType = DbType.Int32;
//            param.Value = year;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@FacCode";
//            param.DbType = DbType.String;
//            param.Value = facilityCode;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@SupportingPartnerId";
//            param.DbType = DbType.Int32;
//            param.Value = supportingPartnerId;
//            command.Parameters.Add(param);

//            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
//            return dataTable;
//        }

        private static DataTable GetMissingDistrictReport(int districtID, int report,
            int year, int period, int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name], 
                @Year AS Year, @Period AS Period FROM Facilities INNER JOIN ExpectedReports ON 
                Facilities.FacCode = ExpectedReports.FacCode WHERE (Facilities.FacCode NOT IN
                (SELECT FacCode FROM ReceivedSMS WHERE (RepID = @RepID) AND (Period = @Period) AND (Year = @Year))) 
                AND (District = @DistrictID) AND (ExpectedReports.ReportID = @RepID) 
                AND (CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS datetime) 
                >= ExpectedReports.ReportingStartDate) AND (Facilities.Activated = 1) 
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DistrictID";
            param.DbType = DbType.Int32;
            param.Value = districtID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);

            // This is a special requirement for quarterly MTUHA MC if applicable.
            if (((period % Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS) != 0) && dataTable.Rows.Count > 0)
            {
                var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                if (!string.IsNullOrWhiteSpace(s))
                {
                    int reportId;
                    bool converted = int.TryParse(s, out reportId);
                    if (converted && report == reportId) dataTable.Rows.Clear();
                }
            }

            return dataTable;
        }

        private static DataTable GetMissingRegionReport(int regionID, int report,
            int year, int period, int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name], 
                @Year AS Year, @Period AS Period FROM Facilities INNER JOIN ExpectedReports ON 
                Facilities.FacCode = ExpectedReports.FacCode WHERE (Facilities.FacCode NOT IN
                (SELECT FacCode FROM ReceivedSMS WHERE (RepID = @RepID) AND (Period = @Period) AND (Year = @Year))) 
                AND (Region = @RegionID) AND (ExpectedReports.ReportID = @RepID) 
                AND (CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS datetime) 
                >= ExpectedReports.ReportingStartDate) AND (Facilities.Activated = 1) 
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            //            command.CommandText = @"SELECT Facilities.FacCode, FacName, @Year AS [Year], @Period AS Period 
            //                FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode
            //                WHERE (Facilities.FacCode NOT IN (SELECT FacCode FROM ReceivedSMS 
            //                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) 
            //                AND (Region = @RegionID) AND (ExpectedReports.ReportID = @RepID)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@RegionID";
            param.DbType = DbType.Int32;
            param.Value = regionID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            // This is a special requirement for quarterly MTUHA MC if applicable.
            if (((period % Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS) != 0) && dataTable.Rows.Count > 0)
            {
                var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                if (!string.IsNullOrWhiteSpace(s))
                {
                    int reportId;
                    bool converted = int.TryParse(s, out reportId);
                    if (converted && report == reportId) dataTable.Rows.Clear();
                }
            }
            return dataTable;
        }

        private static DataTable GetMissingNationReport(int report, int year, int period, 
            int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name], 
                @Year AS Year, @Period AS Period FROM Facilities INNER JOIN ExpectedReports ON 
                Facilities.FacCode = ExpectedReports.FacCode WHERE (Facilities.FacCode NOT IN
                (SELECT FacCode FROM ReceivedSMS WHERE (RepID = @RepID) AND (Period = @Period) AND (Year = @Year))) 
                AND (ExpectedReports.ReportID = @RepID) 
                AND (CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS datetime) 
                >= ExpectedReports.ReportingStartDate) AND (Facilities.Activated = 1) 
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            //            command.CommandText = @"SELECT Facilities.FacCode, FacName, @Year AS [Year], @Period AS Period 
            //                FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode
            //                WHERE (Facilities.FacCode NOT IN (SELECT FacCode FROM ReceivedSMS 
            //                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) AND (ExpectedReports.ReportID = @RepID)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            // This is a special requirement for quarterly MTUHA MC if applicable.
            if (((period % Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS) != 0) && dataTable.Rows.Count > 0)
            {
                var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                if (!string.IsNullOrWhiteSpace(s))
                {
                    int reportId;
                    bool converted = int.TryParse(s, out reportId);
                    if (converted && report == reportId) dataTable.Rows.Clear();
                }
            }
            return dataTable;
        }

        public static DataTable GetMissingDistrictReport(int districtID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetMissingDistrictReport(districtID,
                                    report, currentYear, currentPeriod, supportingPartnerId, command);
                                else
                                {
                                    DataTable temp = GetMissingDistrictReport(districtID, report,
                                        currentYear, currentPeriod, supportingPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetMissingDistrictReportBySupportingPartner(districtID, 
                        report, startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        public static DataTable GetMissingRegionReport(int regionID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetMissingRegionReport(regionID,
                                    report, currentYear, currentPeriod, supportingPartnerId, command);
                                else
                                {
                                    DataTable temp = GetMissingRegionReport(regionID, report,
                                        currentYear, currentPeriod, supportingPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetMissingRegionReportBySupportingPartner(regionID, 
                        report, startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        public static DataTable GetMissingNationReport(int report, int startYear,
            int startPeriod, int endYear, int endPeriod,  int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetMissingNationReport(report,
                                    currentYear, currentPeriod, supportingPartnerId, command);
                                else
                                {
                                    DataTable temp = GetMissingNationReport(report,
                                        currentYear, currentPeriod, supportingPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetMissingNationReportBySupportingPartner(report, startYear,
                        startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }
        #endregion

        #region Submitted Reports
        private static DataTable GetSubmittedDistrictReport(int districtID, int report,
            int year, int period, int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT FacCode, FacName, @Year AS [Year], @Period AS Period 
                FROM Facilities WHERE (FacCode IN (SELECT FacCode FROM ReceivedSMS 
                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) 
                AND (District = @DistrictID) AND (Facilities.Activated = 1)
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DistrictID";
            param.DbType = DbType.Int32;
            param.Value = districtID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            return dataTable;
        }

        public static DataTable GetSubmittedDistrictReport(int districtID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetSubmittedDistrictReport(districtID,
                                    report, currentYear, currentPeriod, supportingPartnerId, command);
                                else
                                {
                                    DataTable temp = GetSubmittedDistrictReport(districtID, report,
                                        currentYear, currentPeriod, supportingPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetSubmittedDistrictReportBySupportingPartner(districtID, 
                        report, startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        public static DataTable GetSubmittedRegionReport(int regionID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }

                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetSubmittedRegionReport(regionID,
                                    report, currentYear, currentPeriod, supportingPartnerId, command);
                                else
                                {
                                    DataTable temp = GetSubmittedRegionReport(regionID, report,
                                        currentYear, currentPeriod, supportingPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetSubmittedRegionReportBySupportingPartner(regionID, 
                        report, startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        private static DataTable GetSubmittedRegionReport(int regionID, int report,
            int year, int period, int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT FacCode, FacName, @Year AS [Year], @Period AS Period 
                FROM Facilities WHERE (FacCode IN (SELECT FacCode FROM ReceivedSMS 
                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) 
                AND (Region = @RegionID) AND (Facilities.Activated = 1)
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@RegionID";
            param.DbType = DbType.Int32;
            param.Value = regionID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            return dataTable;
        }
        
        private static DataTable GetSubmittedNationReport(int report, int year, int period,
            int supportingPartnerId, DbCommand command)
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT FacCode, FacName, @Year AS [Year], @Period AS Period 
                FROM Facilities WHERE (FacCode IN (SELECT FacCode FROM ReceivedSMS 
                WHERE (RepID = @RepID) AND (Period = @Period) AND ([Year] = @Year))) AND (Facilities.Activated = 1) 
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            command.Parameters.Clear();
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@RepID";
            param.DbType = DbType.Int32;
            param.Value = report;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Period";
            param.DbType = DbType.Int32;
            param.Value = period;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            return dataTable;
        }

        public static DataTable GetSubmittedNationReport(int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportintPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    int currentPeriod = startPeriod;
                    int currentYear = startYear;
                    do
                    {
                        if (currentYear > endYear) break;
                        else
                        {
                            if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                            else
                            {
                                if (reportCount == null) reportCount = GetSubmittedNationReport(report,
                                    currentYear, currentPeriod, supportintPartnerId, command);
                                else
                                {
                                    DataTable temp = GetSubmittedNationReport(report,
                                        currentYear, currentPeriod, supportintPartnerId, command);
                                    reportCount.Merge(temp);
                                }
                            }
                        }

                        currentPeriod++;
                        if (currentPeriod > 12)
                        {
                            currentPeriod = 1;
                            currentYear++;
                        }
                    } while (true);
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetSubmittedNationReportBySupportingPartner(report, 
                        startYear, startPeriod, endYear, endPeriod, supportintPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }        
        #endregion

        #region Getting Report Data
//        private static DataTable GetFacilityReport(string facilityCode, int reportID, int year,
//            int startPeriod, int endPeriod, bool basicInfo, int supportingPartnerId)
//        {
//            DataTable dataTable = null;
//            DbCommand command = null;

//            switch (AppConfiguration.AccessMethod)
//            {
//                case DataAccessMethod.DIRECT_ACCESS:
//                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
//                    command = GenericDataAccess.CreateCommand();
//                    command.CommandType = CommandType.Text;
//                    if (basicInfo)
//                    {
//                        command.CommandText = @"SELECT ShortCode AS [Data Description], (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData
//                    FROM ReportData INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo
//                    WHERE (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) AND 
//                    (ReportData.RepID = rdl.RepID) AND (ReportData.DataPosition = rdl.DataPosition) AND (ReceivedSMS.FacCode = @FacilityCode)) AS Data
//                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
//                    }
//                    else
//                    {
//                        command.CommandText = @"SELECT DataDescription AS [Data Description], (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData
//                    FROM ReportData INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo
//                    WHERE (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) AND 
//                    (ReportData.RepID = rdl.RepID) AND (ReportData.DataPosition = rdl.DataPosition) AND (ReceivedSMS.FacCode = @FacilityCode)) AS Data
//                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
//                    }
//                    break;

//                case DataAccessMethod.OFFLINE_DATA_ACCESS:
//                    command = GenericDataAccess.CreateCacheDBCommand();
//                    command.CommandType = CommandType.Text;
//                    if (basicInfo)
//                    {
//                        command.CommandText = @"SELECT ShortCode AS [Data Description], (SELECT SUM(ReportData.Data) AS ReportData
//                    FROM ReportData INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo
//                    WHERE (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) AND 
//                    (ReportData.RepID = rdl.RepID) AND (ReportData.DataPosition = rdl.DataPosition) AND (ReceivedSMS.FacCode = @FacilityCode)) AS Data
//                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
//                    }
//                    else
//                    {
//                        command.CommandText = @"SELECT DataDescription AS [Data Description], (SELECT SUM(ReportData.Data) AS ReportData
//                    FROM ReportData INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo
//                    WHERE (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) AND 
//                    (ReportData.RepID = rdl.RepID) AND (ReportData.DataPosition = rdl.DataPosition) AND (ReceivedSMS.FacCode = @FacilityCode)) AS Data
//                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
//                    }
//                    break;
//            }

//            #region Setting Parameters
//            DbParameter param = command.CreateParameter();
//            param.ParameterName = "@Year";
//            param.DbType = DbType.Int32;
//            param.Value = year;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@StartPeriod";
//            param.DbType = DbType.Int32;
//            param.Value = startPeriod;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@EndPeriod";
//            param.DbType = DbType.Int32;
//            param.Value = endPeriod;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@FacilityCode";
//            param.DbType = DbType.String;
//            param.Value = facilityCode;
//            command.Parameters.Add(param);

//            param = command.CreateParameter();
//            param.ParameterName = "@ReportID";
//            param.DbType = DbType.Int32;
//            param.Value = reportID;
//            command.Parameters.Add(param);
//            #endregion
//            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
//            return dataTable;
//        }

        private static DataTable GetDistrictReport(int districtID, int reportID, int year,
            int startPeriod, int endPeriod, bool basicInfo, int supportingPartnerId)
        {
            DataTable dataTable = null;
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE (ReceivedSMS.Year = @Year) AND 
                    (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition)
                    AND (Facilities.District = @DistrictID) AND (Facilities.Activated = 1) 
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE (ReceivedSMS.Year = @Year) AND 
                    (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition)
                    AND (Facilities.District = @DistrictID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE (ReceivedSMS.Year = @Year) AND 
                    (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition)
                    AND (Facilities.District = @DistrictID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE (ReceivedSMS.Year = @Year) AND 
                    (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition)
                    AND (Facilities.District = @DistrictID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;
            }


            #region Setting Parameters
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@StartPeriod";
            param.DbType = DbType.Int32;
            param.Value = startPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@EndPeriod";
            param.DbType = DbType.Int32;
            param.Value = endPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DistrictID";
            param.DbType = DbType.Int32;
            param.Value = districtID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@ReportID";
            param.DbType = DbType.Int32;
            param.Value = reportID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);
            #endregion
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        private static DataTable GetRegionReport(int regionID, int reportID, int year,
            int startPeriod, int endPeriod, bool basicInfo, int supportingPartnerId)
        {
            DataTable dataTable = null;
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition) 
                    AND (Facilities.Region = @RegionID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition) 
                    AND (Facilities.Region = @RegionID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition) 
                    AND (Facilities.Region = @RegionID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.RepID = rdl.RepID)  AND (ReportData.DataPosition = rdl.DataPosition) 
                    AND (Facilities.Region = @RegionID) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;
            }


            #region Setting Parameters
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@StartPeriod";
            param.DbType = DbType.Int32;
            param.Value = startPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@EndPeriod";
            param.DbType = DbType.Int32;
            param.Value = endPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@RegionID";
            param.DbType = DbType.Int32;
            param.Value = regionID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@ReportID";
            param.DbType = DbType.Int32;
            param.Value = reportID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);
            #endregion
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        private static DataTable GetNationReport(int reportID, int year, int startPeriod,
            int endPeriod, bool basicInfo, int supportingPartnerId)
        {
            DataTable dataTable = null;
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (Facilities.Activated = 1) 
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)                    
                    AND  (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.DataPosition = rdl.DataPosition) AND (ReportData.RepID = rdl.RepID)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT ISNULL(SUM(CAST(ReportData.Data AS INT)), 0) AS ReportData FROM ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (Facilities.Activated = 1)
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId) 
                    AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.DataPosition = rdl.DataPosition) AND (ReportData.RepID = rdl.RepID)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    if (basicInfo)
                    {
                        command.CommandText = @"SELECT ShortCode AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (Facilities.Activated = 1) 
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                    AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.DataPosition = rdl.DataPosition) AND (ReportData.RepID = rdl.RepID)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    else
                    {
                        command.CommandText = @"SELECT DataDescription AS [Data Description], 
                    (SELECT SUM(ReportData.Data) AS ReportData FROM (ReportData 
                    INNER JOIN ReceivedSMS ON ReportData.ReceivedSMSNo = ReceivedSMS.ReceivedSMSNo) INNER JOIN
                    Facilities ON ReceivedSMS.FacCode = Facilities.FacCode WHERE 
                    (ReceivedSMS.Year = @Year) AND (Facilities.Activated = 1) 
                    AND (Facilities.SupportingPartnerId = @SupportingPartnerId) 
                    AND (ReceivedSMS.Period BETWEEN @StartPeriod AND @EndPeriod) 
                    AND (ReportData.DataPosition = rdl.DataPosition) AND (ReportData.RepID = rdl.RepID)) AS Data
                    FROM ReportDataLocations AS rdl WHERE (RepID = @ReportID) AND (DataPosition > 3)";
                    }
                    break;
            }


            #region Setting Parameters
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Year";
            param.DbType = DbType.Int32;
            param.Value = year;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@StartPeriod";
            param.DbType = DbType.Int32;
            param.Value = startPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@EndPeriod";
            param.DbType = DbType.Int32;
            param.Value = endPeriod;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@ReportID";
            param.DbType = DbType.Int32;
            param.Value = reportID;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@SupportingPartnerId";
            param.DbType = DbType.Int32;
            param.Value = supportingPartnerId;
            command.Parameters.Add(param);
            #endregion
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }
        #endregion

        #region Report Count
        public static DataTable GetDistrictReportCount(int districtID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    command.CommandType = CommandType.Text;

                    reportCount = new DataTable();
                    DataColumn column = new DataColumn("Year", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Period", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Report Count", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);

                    command.CommandText = @"SELECT FacCode FROM Facilities WHERE District=@DistrictID 
                        AND (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
                    DbParameter param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = districtID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    DataTable facilities = GenericDataAccess.ExecuteSelectCommand(command, false);

                    if (facilities.Rows.Count > 0)
                    {
                        // This is a special requirement for quarterly MTUHA MC if applicable
                        int mtuhaQuarterReportId = -1;
                        int quarterInterval = Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS;
                        var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                        if (!string.IsNullOrWhiteSpace(s))
                        {
                            bool converted = int.TryParse(s, out mtuhaQuarterReportId);
                            if (!converted) mtuhaQuarterReportId = -1;
                        }

                        int currentPeriod = startPeriod;
                        int currentYear = startYear;
                        DataRow row = null;

                        if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) { }
                        else
                        {
                            row = reportCount.NewRow();
                            row["Year"] = startYear;
                            row["Period"] = startPeriod;
                            int count = 0;
                            for (int i = 0; i < facilities.Rows.Count; i++)
                            {
                                string code = facilities.Rows[i][0].ToString();
                                count += GetFacilityReportCount(code, report,
                                    startYear, startPeriod, command);
                            }
                            row["Report Count"] = count;
                            reportCount.Rows.Add(row);
                        }

                        do
                        {
                            currentPeriod++;
                            if (currentPeriod > 12)
                            {
                                currentPeriod = 1;
                                currentYear++;
                            }

                            if (currentYear > endYear) break;
                            else
                            {
                                if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                                else
                                {
                                    if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) continue;
                                    else
                                    {
                                        row = reportCount.NewRow();
                                        row["Year"] = currentYear;
                                        row["Period"] = currentPeriod;

                                        int count = 0;
                                        for (int i = 0; i < facilities.Rows.Count; i++)
                                        {
                                            string code = facilities.Rows[i][0].ToString();
                                            count += GetFacilityReportCount(code, report,
                                                currentYear, currentPeriod, command);
                                        }
                                        row["Report Count"] = count;
                                        reportCount.Rows.Add(row);
                                    }
                                }
                            }
                        } while (true);
                    }
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetDistrictReportCountBySupportingPartner(districtID, 
                        report, startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        public static DataTable GetRegionReportCount(int regionID, int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                    }
                    command.CommandType = CommandType.Text;

                    reportCount = new DataTable();
                    DataColumn column = new DataColumn("Year", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Period", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Report Count", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);

                    command.CommandText = @"SELECT FacCode FROM Facilities WHERE Region=@RegionID AND 
                        (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
                    DbParameter param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    DataTable facilities = GenericDataAccess.ExecuteSelectCommand(command, false);

                    if (facilities.Rows.Count > 0)
                    {
                        // This is a special requirement for quarterly MTUHA MC if applicable
                        int mtuhaQuarterReportId = -1;
                        int quarterInterval = Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS;
                        var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                        if (!string.IsNullOrWhiteSpace(s))
                        {
                            bool converted = int.TryParse(s, out mtuhaQuarterReportId);
                            if (!converted) mtuhaQuarterReportId = -1;
                        }

                        int currentPeriod = startPeriod;
                        int currentYear = startYear;
                        DataRow row = null;

                        if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) { }
                        else
                        {
                            row = reportCount.NewRow();
                            row["Year"] = startYear;
                            row["Period"] = startPeriod;
                            int count = 0;
                            for (int i = 0; i < facilities.Rows.Count; i++)
                            {
                                string code = facilities.Rows[i][0].ToString();
                                count += GetFacilityReportCount(code, report,
                                    startYear, startPeriod, command);
                            }
                            row["Report Count"] = count;
                            reportCount.Rows.Add(row);
                        }

                        do
                        {
                            currentPeriod++;
                            if (currentPeriod > 12)
                            {
                                currentPeriod = 1;
                                currentYear++;
                            }

                            if (currentYear > endYear) break;
                            else
                            {
                                if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                                else
                                {
                                    if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) continue;
                                    else
                                    {
                                        row = reportCount.NewRow();
                                        row["Year"] = currentYear;
                                        row["Period"] = currentPeriod;

                                        int count = 0;
                                        for (int i = 0; i < facilities.Rows.Count; i++)
                                        {
                                            string code = facilities.Rows[i][0].ToString();
                                            count += GetFacilityReportCount(code, report,
                                                currentYear, currentPeriod, command);
                                        }
                                        row["Report Count"] = count;
                                        reportCount.Rows.Add(row);
                                    }
                                }
                            }
                        } while (true);
                    }
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionReportCountBySupportingPartner(regionID, report, 
                        startYear, startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }

        public static DataTable GetNationReportCount(int report, int startYear,
            int startPeriod, int endYear, int endPeriod, int supportingPartnerId)
        {
            DataTable reportCount = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = null;
                    if ((AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS) ||
                        (AppConfiguration.AccessMethod == DataAccessMethod.OFFLINE_DIRECT_ACCESS))
                    {
                        command = GenericDataAccess.CreateCommand();
                        command.CommandType = CommandType.Text;
                    }
                    else
                    {
                        command = GenericDataAccess.CreateCacheDBCommand();
                        command.CommandType = CommandType.Text;
                    }

                    reportCount = new DataTable();
                    DataColumn column = new DataColumn("Year", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Period", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);
                    column = new DataColumn("Report Count", Type.GetType("System.Int32"));
                    reportCount.Columns.Add(column);

                    command.CommandText = @"SELECT FacCode FROM Facilities WHERE (Facilities.Activated = 1)
                        AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);


                    DataTable facilities = GenericDataAccess.ExecuteSelectCommand(command, false);

                    if (facilities.Rows.Count > 0)
                    {
                        // This is a special requirement for quarterly MTUHA MC if applicable
                        int mtuhaQuarterReportId = -1;
                        int quarterInterval = Configurations.SystemSetting.MTUHA_MC_REPORT_INTERVAL_MONTHS;
                        var s = Configurations.SystemSetting.GetSetting(Configurations.SystemConfig.MTUHA_MC_REPORT_ID);
                        if (!string.IsNullOrWhiteSpace(s))
                        {
                            bool converted = int.TryParse(s, out mtuhaQuarterReportId);
                            if (!converted) mtuhaQuarterReportId = -1;
                        }

                        int currentPeriod = startPeriod;
                        int currentYear = startYear;
                        DataRow row = null;

                        if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) { }
                        else
                        {
                            row = reportCount.NewRow();
                            row["Year"] = startYear;
                            row["Period"] = startPeriod;
                            int count = 0;
                            for (int i = 0; i < facilities.Rows.Count; i++)
                            {
                                string code = facilities.Rows[i][0].ToString();
                                count += GetFacilityReportCount(code, report,
                                    startYear, startPeriod, command);
                            }
                            row["Report Count"] = count;
                            reportCount.Rows.Add(row);
                        }

                        do
                        {
                            currentPeriod++;
                            if (currentPeriod > 12)
                            {
                                currentPeriod = 1;
                                currentYear++;
                            }

                            if (currentYear > endYear) break;
                            else
                            {
                                if ((currentYear == endYear) && (currentPeriod > endPeriod)) break;
                                else
                                {
                                    if (report == mtuhaQuarterReportId && ((currentPeriod % quarterInterval) != 0)) continue;
                                    else
                                    {
                                        row = reportCount.NewRow();
                                        row["Year"] = currentYear;
                                        row["Period"] = currentPeriod;

                                        int count = 0;
                                        for (int i = 0; i < facilities.Rows.Count; i++)
                                        {
                                            string code = facilities.Rows[i][0].ToString();
                                            count += GetFacilityReportCount(code, report,
                                                currentYear, currentPeriod, command);
                                        }
                                        row["Report Count"] = count;
                                        reportCount.Rows.Add(row);
                                    }
                                }
                            }
                        } while (true);
                    }
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetNationReportCountBySupportingPartner(report, startYear,
                        startPeriod, endYear, endPeriod, supportingPartnerId);
                    reportCount = DataTransformation.Deserialize(output);
                    break;
            }
            return reportCount;
        }
        #endregion

        #region GetReport Methods with Supporting Partner Fix
        public static DataTable GetDistrictReport(int districtID, int reportID, int startYear, int startPeriod,
            int endYear, int endPeriod, int supportingPartnerId, bool basicInfo = false)
        {
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    if (startYear == endYear)
                    {
                        dataTable = GetDistrictReport(districtID, reportID, startYear, startPeriod, 
                            endPeriod, basicInfo, supportingPartnerId);
                    }
                    else
                    {
                        int currentYear = startYear;
                        do
                        {
                            int startingPeriod = 1;
                            int endingPeriod = 12;
                            if (currentYear == startYear) startingPeriod = startPeriod;
                            if (currentYear == endYear) endingPeriod = endPeriod;

                            DataTable temp = GetDistrictReport(districtID, reportID, currentYear,
                                startingPeriod, endingPeriod, basicInfo, supportingPartnerId);
                            if (dataTable == null)
                            {
                                dataTable = temp;
                                dataTable.Columns[1].ReadOnly = false;
                            }
                            else
                            {
                                for (int i = 0; i < dataTable.Rows.Count; i++)
                                {
                                    DataRow row = dataTable.Rows[i];
                                    DataRow tempRow = temp.Rows[i];
                                    int value = Int32.Parse(row[1].ToString());
                                    int tempValue = Int32.Parse(tempRow[1].ToString());
                                    int newValue = value + tempValue;
                                    row[1] = newValue;
                                }
                            }
                            currentYear++;
                        } while (currentYear <= endYear);
                    }
                    dataTable.Columns[1].ReadOnly = true;
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetDistrictReportBySupportingPartner(districtID, reportID, startYear,
                        startPeriod, endYear, endPeriod, supportingPartnerId, basicInfo);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }

            return dataTable;
        }

        public static DataTable GetRegionReport(int regionID, int reportID, int startYear, int startPeriod,
            int endYear, int endPeriod, int supportingPartnerId, bool basicInfo = false)
        {
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    if (startYear == endYear)
                    {
                        dataTable = GetRegionReport(regionID, reportID, startYear, startPeriod, 
                            endPeriod, basicInfo, supportingPartnerId);
                    }
                    else
                    {
                        int currentYear = startYear;
                        do
                        {
                            int startingPeriod = 1;
                            int endingPeriod = 12;
                            if (currentYear == startYear) startingPeriod = startPeriod;
                            if (currentYear == endYear) endingPeriod = endPeriod;

                            DataTable temp = GetRegionReport(regionID, reportID, currentYear,
                                startingPeriod, endingPeriod, basicInfo, supportingPartnerId);
                            if (dataTable == null)
                            {
                                dataTable = temp;
                                dataTable.Columns[1].ReadOnly = false;
                            }
                            else
                            {
                                for (int i = 0; i < dataTable.Rows.Count; i++)
                                {
                                    DataRow row = dataTable.Rows[i];
                                    DataRow tempRow = temp.Rows[i];
                                    int value = Int32.Parse(row[1].ToString());
                                    int tempValue = Int32.Parse(tempRow[1].ToString());
                                    int newValue = value + tempValue;
                                    row[1] = newValue;
                                }
                            }
                            currentYear++;
                        } while (currentYear <= endYear);
                    }
                    dataTable.Columns[1].ReadOnly = true;
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionReportBySupportingPartner(regionID, reportID, 
                        startYear, startPeriod, endYear, endPeriod, supportingPartnerId, basicInfo);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }

            return dataTable;
        }

        public static DataTable GetNationReport(int reportID, int startYear, int startPeriod, int endYear,
            int endPeriod, int supportingPartnerId, bool basicInfo = false)
        {
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    if (startYear == endYear)
                    {
                        dataTable = GetNationReport(reportID, startYear, startPeriod, endPeriod, 
                            basicInfo, supportingPartnerId);
                    }
                    else
                    {
                        int currentYear = startYear;
                        do
                        {
                            int startingPeriod = 1;
                            int endingPeriod = 12;
                            if (currentYear == startYear) startingPeriod = startPeriod;
                            if (currentYear == endYear) endingPeriod = endPeriod;

                            DataTable temp = GetNationReport(reportID, currentYear,
                                startingPeriod, endingPeriod, basicInfo, supportingPartnerId);
                            if (dataTable == null)
                            {
                                dataTable = temp;
                                dataTable.Columns[1].ReadOnly = false;
                            }
                            else
                            {
                                for (int i = 0; i < dataTable.Rows.Count; i++)
                                {
                                    DataRow row = dataTable.Rows[i];
                                    DataRow tempRow = temp.Rows[i];
                                    int value = Int32.Parse(row[1].ToString());
                                    int tempValue = Int32.Parse(tempRow[1].ToString());
                                    int newValue = value + tempValue;
                                    row[1] = newValue;
                                }
                            }
                            currentYear++;
                        } while (currentYear <= endYear);
                    }
                    dataTable.Columns[1].ReadOnly = true;
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetNationReportBySupportingPartner(reportID, startYear,
                        startPeriod, endYear, endPeriod, supportingPartnerId, basicInfo);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }
        #endregion

        #region Reminders
        public static DataTable GetFacilitiesWithoutReport(int reportID, int period,
            int year, int supportingPartnerId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID)";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);
                    
                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutReportBySupportingPartner(reportID, 
                        period, year, supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int reportID, int period,
            int year, int supportingPartnerId, int displayFacilitiesFromDistrictId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.District = @DistrictId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID)AND (Facilities.District = @DistrictId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutReportFilteredByDistrictBySupportingPartner(
                        reportID, period, year, supportingPartnerId, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int reportID, int period,
            int year, int supportingPartnerId, int displayFacilitiesFromRegionId, bool filter)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region = @RegionId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region = @RegionId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutReportFilteredByRegionBySupportingPartner(
                        reportID, period, year, supportingPartnerId, displayFacilitiesFromRegionId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period,
            int year, int supportingPartnerId, bool atLeastOne)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    
                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReportBySupportingPartner(period, 
                        year, supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period,
            int year, int supportingPartnerId, int displayFacilitiesFromDistrictId, bool atLeastOne)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.District = @DistrictId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID  
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.District = @DistrictId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReportFilteredByDistrictBySupportingPartner(
                        period, year, supportingPartnerId, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period,
            int year, int supportingPartnerId, bool atLeastOne, int displayFacilitiesFromRegionId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReportFilteredByRegionBySupportingPartner(
                        period, year, supportingPartnerId, displayFacilitiesFromRegionId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period, int year)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReport(period, year);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period, int year, 
            int displayFacilitiesFromDistrictId, string filter)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.District = @DistrictId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.District = @DistrictId) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReportFilteredByDistrict(
                        period, year, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int period, int year, 
            string filter, int displayFacilitiesFromRegionId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutAtLeastOneReportFilteredByRegion(
                        period, year, displayFacilitiesFromRegionId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetDistrictFacilitiesWithoutReport(int districtID, int period, int year)
        {
            DataTable dataTable = null;
            DbCommand command = null;
            DbParameter param = null;
            string query = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.District=@DistrictID)
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = districtID;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.District=@DistrictID)
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = districtID;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetDistrictFacilitiesWithoutAtLeastOneReport(districtID, period, year);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int reportID, int period, int year,
            int displayFacilitiesFromDistrictId, string filter)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.District = @DistrictId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.District = @DistrictId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutReportFilteredByDistrict(
                        reportID, period, year, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetFacilitiesWithoutReport(int reportID, int period, int year,
            string filter, int displayFacilitiesFromRegionId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region = @RegionId) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionId";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromRegionId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetFacilitiesWithoutReportFilteredByRegion(
                        reportID, period, year, displayFacilitiesFromRegionId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetRegionFacilitiesWithoutReport(int regionID, int reportID,
            int period, int year, int supportingPartnerId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1)  AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region=@RegionID) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1)  AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region=@RegionID)
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionFacilitiesWithoutReportBySupportingPartner(regionID, 
                        reportID, period, year, supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetRegionFacilitiesWithoutReport(int regionID, int reportID,
            int period, int year, int supportingPartnerId, int displayFacilitiesFromDistrictId)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1)  AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region=@RegionID) 
                        AND (Facilities.District=@DistrictID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT Facilities.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name] 
                        FROM Facilities INNER JOIN ExpectedReports ON Facilities.FacCode = ExpectedReports.FacCode 
                        WHERE (Facilities.Activated = 1)  AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.FacCode NOT IN (SELECT DISTINCT ExpectedReports_1.FacCode FROM ExpectedReports 
                        AS ExpectedReports_1 INNER JOIN ReceivedSMS ON ExpectedReports_1.FacCode = ReceivedSMS.FacCode 
                        WHERE (ReceivedSMS.RepID = @RepID) AND (ReceivedSMS.Period = @Period) AND (ReceivedSMS.Year = @Year))) 
                        AND (ExpectedReports.ReportID = @RepID) AND (Facilities.Region=@RegionID)
                        AND (Facilities.District=@DistrictID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@RepID";
                    param.DbType = DbType.Int32;
                    param.Value = reportID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionFacilitiesWithoutReportFilteredByDistrictBySupportingPartner(
                        regionID, reportID, period, year, supportingPartnerId, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetRegionFacilitiesWithoutReport(int regionID, int period, int year)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionFacilitiesWithoutAtLeastOneReport(regionID, period, year);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetRegionFacilitiesWithoutReport(int regionID, int period, int year, 
            int supportingPartnerId, bool atLeastOne)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID) ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    
                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionFacilitiesWithoutAtLeastOneReportBySupportingPartner(regionID,
                        period, year, supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetRegionFacilitiesWithoutReport(int regionID, int period, int year,
            int supportingPartnerId, int displayFacilitiesFromDistrictId, bool atLeastOne)
        {
            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID  
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID) AND (Facilities.District = @DistrictID) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT DISTINCT M.FacCode AS [Facility Code], Facilities.FacName AS [Facility Name]
                        FROM (SELECT FacCode, ReportID FROM ExpectedReports INNER JOIN Report ON ExpectedReports.ReportID=Report.RepID 
                        WHERE (Report.UsedAsView = 1) AND
                        (ReportingStartDate < CAST(CAST(@Year AS varchar(4)) + '-' + CAST(@Period AS varchar(2)) + '-01' AS DATETIME))
                        EXCEPT
                        SELECT FacCode, RepID FROM ReceivedSMS WHERE (Year = @Year) AND (Period = @Period)) AS M
                        INNER JOIN Facilities ON M.FacCode = Facilities.FacCode 
                        WHERE (Facilities.Activated = 1) AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        AND (Facilities.Region = @RegionID)  AND (Facilities.District = @DistrictID) 
                        ORDER BY [Facility Name] ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@Period";
                    param.DbType = DbType.Int32;
                    param.Value = period;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@Year";
                    param.DbType = DbType.Int32;
                    param.Value = year;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionID";
                    param.DbType = DbType.Int32;
                    param.Value = regionID;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@DistrictID";
                    param.DbType = DbType.Int32;
                    param.Value = displayFacilitiesFromDistrictId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegionFacilitiesWithoutAtLeastOneReportFilteredByDistrictBySupportingPartner(
                        regionID, period, year, supportingPartnerId, displayFacilitiesFromDistrictId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        #endregion

        #region Assistance
        public static DataTable GetRegisteredUsers(int supportingPartnerId)
        {
            // TODO: This method does not return all registered users instead SI registered users
            if (AppConfiguration.DataCached)
            {
                if (cache_RegisteredUsers != null) return cache_RegisteredUsers;
            }

            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT ApprovedCellNo, CellNum, LTRIM(RTRIM(Name)) AS Name, LangID, SendSMS, 
                        FacCode FROM ApprovedSMSCells WHERE TitleID = 4 ORDER BY Name";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT ApprovedCellNo, CellNum, [Name], LangID, SendSMS, 
                        FacCode FROM ApprovedSMSCells WHERE TitleID = 4 ORDER BY [Name]";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetRegisteredUsers();
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            if (AppConfiguration.DataCached) cache_RegisteredUsers = dataTable;
            return dataTable;
        }

        #endregion

        #region Blocked Reports
        public static DataTable GetBlockedReportsByRegion(int regionId, int supportingPartnerId)
        {
            if (AppConfiguration.DataCached)
            {
                if (cache_BlockedReports != null) return cache_BlockedReports;
            }
            DataTable blockedReports = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    //                    string query = @"SELECT ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, es.RepID, r.ReportName, 
                    //                        es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, f.FacName, rg.RegionName FROM ErrMsgSMS es
                    //                        INNER JOIN ApprovedSMSCells ac ON ac.CellNum = es.SenderNum INNER JOIN Report r on r.RepID = es.RepID
                    //                        INNER JOIN Facilities f on f.FacCode = es.FacCode INNER JOIN Region rg on rg.RegionNo = f.Region
                    //                        ORDER BY DateReceived DESC";
                    query = @"SELECT es.ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, 
                        es.RepID, r.ReportName, es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, 
                        f.FacName, rg.RegionName, District.District AS DistrictName FROM Region AS rg 
                        INNER JOIN Facilities AS f ON rg.RegionNo = f.Region INNER JOIN District 
                        ON f.District = District.DistrictNo RIGHT OUTER JOIN ErrMsgSMS AS es ON f.FacCode = es.FacCode 
                        LEFT OUTER JOIN Report AS r ON r.RepID = es.RepID LEFT OUTER JOIN 
                        ApprovedSMSCells AS ac ON ac.CellNum = es.SenderNum 
                        WHERE (es.SenderNum IN
                            (SELECT SenderNum FROM 
                                (SELECT SenderNum, COUNT(SenderNum) AS count
                                FROM ErrMsgSMS GROUP BY SenderNum) AS errorCount 
                                WHERE (count >= @MaxErrorCount))) 
                        AND (es.Deleted<>1 OR es.Deleted IS NULL) AND (rg.RegionNo = @RegionNo) 
                        AND (f.SupportingPartnerId = @SupportingPartnerId)
                        ORDER BY es.DateReceived DESC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionNo";
                    param.DbType = DbType.Int32;
                    param.Value = regionId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    //TODO: Query of getting blocked report for access may miss some records due MS Access join support
                    //RESOLUTION: The query should be divided into subqueries.
                    query = @"SELECT ErrMsgSMS.ReceivedSMSNo, ErrMsgSMS.SenderNum, ApprovedSMSCells.Name, 
                        ErrMsgSMS.DateReceived, Report.RepID, Report.ReportName, ErrMsgSMS.Year, 
                        ErrMsgSMS.Period, ErrMsgSMS.ErrMsg, ErrMsgSMS.TxtDetails, ErrMsgSMS.FacCode, 
                        Facilities.FacName, Region.RegionName, District.District AS DistrictName
                        FROM (Report RIGHT JOIN (((Facilities INNER JOIN ErrMsgSMS ON 
                        Facilities.FacCode = ErrMsgSMS.FacCode) INNER JOIN Region ON 
                        Facilities.Region = Region.RegionNo) INNER JOIN District ON 
                        Facilities.District = District.DistrictNo) ON Report.RepID = ErrMsgSMS.RepID) 
                        LEFT JOIN ApprovedSMSCells ON ErrMsgSMS.SenderNum = ApprovedSMSCells.CellNum 
                        WHERE (ErrMsgSMS.SenderNum IN (SELECT  SenderNum FROM 
                        (SELECT SenderNum, COUNT(SenderNum) AS [count]  FROM ErrMsgSMS GROUP BY SenderNum) 
                        AS errorCount WHERE ([count] >= @MaxErrorCount))) AND 
                        (ErrMsgSMS.Deleted<>1) AND (rg.RegionNo = @RegionNo) OR es.Deleted IS NULL                         
                        AND (Facilities.SupportingPartnerId = @SupportingPartnerId)
                        ORDER BY ErrMsgSMS.DateReceived DESC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@RegionNo";
                    param.DbType = DbType.Int32;
                    param.Value = regionId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetBlockedReportsByRegionBySupportingPartner(regionId, supportingPartnerId);
                    blockedReports = DataTransformation.Deserialize(output);
                    break;
            }
            if (AppConfiguration.DataCached) cache_BlockedReports = blockedReports;
            return blockedReports;
        }

        public static DataTable GetBlockedReportsByDistrict(int districtId, int supportingPartnerId)
        {
            if (AppConfiguration.DataCached)
            {
                if (cache_BlockedReports != null) return cache_BlockedReports;
            }
            DataTable blockedReports = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    //                    string query = @"SELECT ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, es.RepID, r.ReportName, 
                    //                        es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, f.FacName, rg.RegionName FROM ErrMsgSMS es
                    //                        INNER JOIN ApprovedSMSCells ac ON ac.CellNum = es.SenderNum INNER JOIN Report r on r.RepID = es.RepID
                    //                        INNER JOIN Facilities f on f.FacCode = es.FacCode INNER JOIN Region rg on rg.RegionNo = f.Region
                    //                        ORDER BY DateReceived DESC";
                    query = @"SELECT es.ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, 
                        es.RepID, r.ReportName, es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, 
                        f.FacName, rg.RegionName, District.District AS DistrictName FROM Region AS rg 
                        INNER JOIN Facilities AS f ON rg.RegionNo = f.Region INNER JOIN District 
                        ON f.District = District.DistrictNo RIGHT OUTER JOIN ErrMsgSMS AS es ON f.FacCode = es.FacCode 
                        LEFT OUTER JOIN Report AS r ON r.RepID = es.RepID LEFT OUTER JOIN 
                        ApprovedSMSCells AS ac ON ac.CellNum = es.SenderNum WHERE (es.SenderNum IN
                        (SELECT SenderNum FROM (SELECT SenderNum, COUNT(SenderNum) AS count
                        FROM ErrMsgSMS GROUP BY SenderNum) AS errorCount WHERE (count >= @MaxErrorCount))) 
                        AND (es.Deleted<>1 OR es.Deleted IS NULL) AND (District.DistrictNo = @District)                        
                        AND (f.SupportingPartnerId = @SupportingPartnerId) ORDER BY es.DateReceived DESC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@District";
                    param.DbType = DbType.Int32;
                    param.Value = districtId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    //TODO: Query of getting blocked report for access may miss some records due MS Access join support
                    //RESOLUTION: The query should be divided into subqueries.
                    query = @"SELECT ErrMsgSMS.ReceivedSMSNo, ErrMsgSMS.SenderNum, ApprovedSMSCells.Name, 
                        ErrMsgSMS.DateReceived, Report.RepID, Report.ReportName, ErrMsgSMS.Year, 
                        ErrMsgSMS.Period, ErrMsgSMS.ErrMsg, ErrMsgSMS.TxtDetails, ErrMsgSMS.FacCode, 
                        Facilities.FacName, Region.RegionName, District.District AS DistrictName
                        FROM (Report RIGHT JOIN (((Facilities INNER JOIN ErrMsgSMS ON 
                        Facilities.FacCode = ErrMsgSMS.FacCode) INNER JOIN Region ON 
                        Facilities.Region = Region.RegionNo) INNER JOIN District ON 
                        Facilities.District = District.DistrictNo) ON Report.RepID = ErrMsgSMS.RepID) 
                        LEFT JOIN ApprovedSMSCells ON ErrMsgSMS.SenderNum = ApprovedSMSCells.CellNum 
                        WHERE (ErrMsgSMS.SenderNum IN (SELECT  SenderNum FROM 
                        (SELECT SenderNum, COUNT(SenderNum) AS [count]  FROM ErrMsgSMS GROUP BY SenderNum) 
                        AS errorCount WHERE ([count] >= @MaxErrorCount))) AND 
                        (ErrMsgSMS.Deleted<>1) AND (District.DistrictNo = @District)                        
                        AND (Facilities.SupportingPartnerId = @SupportingPartnerId) OR es.Deleted IS NULL ORDER BY ErrMsgSMS.DateReceived DESC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@District";
                    param.DbType = DbType.Int32;
                    param.Value = districtId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetBlockedReportsByDistrictBySupportingPartner(districtId, supportingPartnerId);
                    blockedReports = DataTransformation.Deserialize(output);
                    break;
            }
            if (AppConfiguration.DataCached) cache_BlockedReports = blockedReports;
            return blockedReports;
        }

        public static DataTable GetBlockedReports(int supportingPartnerId)
        {
            if (AppConfiguration.DataCached)
            {
                if (cache_BlockedReports != null) return cache_BlockedReports;
            }
            DataTable blockedReports = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    //                    string query = @"SELECT ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, es.RepID, r.ReportName, 
                    //                        es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, f.FacName, rg.RegionName FROM ErrMsgSMS es
                    //                        INNER JOIN ApprovedSMSCells ac ON ac.CellNum = es.SenderNum INNER JOIN Report r on r.RepID = es.RepID
                    //                        INNER JOIN Facilities f on f.FacCode = es.FacCode INNER JOIN Region rg on rg.RegionNo = f.Region
                    //                        ORDER BY DateReceived DESC";
                    query = @"SELECT es.ReceivedSMSNo, es.SenderNum, ac.Name, es.DateReceived, 
                        es.RepID, r.ReportName, es.Year, es.Period, es.ErrMsg, es.TxtDetails, es.FacCode, 
                        f.FacName, rg.RegionName, District.District AS DistrictName FROM Region AS rg 
                        INNER JOIN Facilities AS f ON rg.RegionNo = f.Region INNER JOIN District 
                        ON f.District = District.DistrictNo RIGHT OUTER JOIN ErrMsgSMS AS es ON f.FacCode = es.FacCode 
                        LEFT OUTER JOIN Report AS r ON r.RepID = es.RepID LEFT OUTER JOIN 
                        ApprovedSMSCells AS ac ON ac.CellNum = es.SenderNum WHERE (es.SenderNum IN
                        (SELECT SenderNum FROM (SELECT SenderNum, COUNT(SenderNum) AS count
                        FROM ErrMsgSMS GROUP BY SenderNum) AS errorCount 
                        WHERE (count >= @MaxErrorCount))) AND (f.SupportingPartnerId = @SupportingPartnerId)
                        AND es.Deleted<>1 OR es.Deleted IS NULL ORDER BY es.DateReceived DESC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    //TODO: Query of getting blocked report for access may miss some records due MS Access join support
                    //RESOLUTION: The query should be divided into subqueries.
                    query = @"SELECT ErrMsgSMS.ReceivedSMSNo, ErrMsgSMS.SenderNum, ApprovedSMSCells.Name, 
                        ErrMsgSMS.DateReceived, Report.RepID, Report.ReportName, ErrMsgSMS.Year, 
                        ErrMsgSMS.Period, ErrMsgSMS.ErrMsg, ErrMsgSMS.TxtDetails, ErrMsgSMS.FacCode, 
                        Facilities.FacName, Region.RegionName, District.District AS DistrictName
                        FROM (Report RIGHT JOIN (((Facilities INNER JOIN ErrMsgSMS ON 
                        Facilities.FacCode = ErrMsgSMS.FacCode) INNER JOIN Region ON 
                        Facilities.Region = Region.RegionNo) INNER JOIN District ON 
                        Facilities.District = District.DistrictNo) ON Report.RepID = ErrMsgSMS.RepID) 
                        LEFT JOIN ApprovedSMSCells ON ErrMsgSMS.SenderNum = ApprovedSMSCells.CellNum 
                        WHERE (ErrMsgSMS.SenderNum IN (SELECT  SenderNum FROM 
                        (SELECT SenderNum, COUNT(SenderNum) AS [count]  FROM ErrMsgSMS GROUP BY SenderNum) 
                        AS errorCount WHERE ([count] >= @MaxErrorCount)))                         
                        AND (Facilities.SupportingPartnerId = @SupportingPartnerId) AND 
                        (ErrMsgSMS.Deleted<>1 OR ErrMsgSMS.Deleted IS NULL) ORDER BY ErrMsgSMS.DateReceived DESC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@MaxErrorCount";
                    param.DbType = DbType.Int32;
                    param.Value = AppConfiguration.MaximumErrorTolerance;
                    command.Parameters.Add(param);
                            
                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    blockedReports = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetBlockedReportsBySupportingPartner(supportingPartnerId);
                    blockedReports = DataTransformation.Deserialize(output);
                    break;
            }
            if (AppConfiguration.DataCached) cache_BlockedReports = blockedReports;
            return blockedReports;
        }
        #endregion

        #region Users
        public static DataTable GetAllUsers(int supportingPartnerId)
        {
            if (AppConfiguration.DataCached)
            {
                if (cache_AllUsers != null) return cache_AllUsers;
            }

            DataTable dataTable = null;
            string query = null;
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT ID, Username, FirstName, LastName, Password, PhoneNumber,
                        EmailAddress, AccessLevelID, AccessLevelValue, SupportingPartnerId 
                        FROM Users WHERE (SupportingPartnerId = @SupportingPartnerId) ORDER BY FirstName ASC";
                    command = GenericDataAccess.CreateCommand();
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT ID, Username, FirstName, LastName, Password, PhoneNumber,
                        EmailAddress, AccessLevelID, AccessLevelValue, SupportingPartnerId 
                        FROM Users WHERE (SupportingPartnerId = @SupportingPartnerId) ORDER BY FirstName ASC";
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAllUsersBySupportingPartner(supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetAllSMSUsersByRegion(int regionId, int supportingPartnerId)
        {
            DataTable dataTable = null;
            string query = @"SELECT ApprovedSMSCells.ApprovedCellNo, ApprovedSMSCells.CellNum, 
                ApprovedSMSCells.Name, ApprovedSMSCells.LangID, ApprovedSMSCells.SendSMS, ApprovedSMSCells.FacCode, 
                ApprovedSMSCells.Email, Facilities.FacName, District.District, Region.RegionName 
                FROM District INNER JOIN Facilities ON District.DistrictNo = Facilities.District INNER JOIN
                Region ON Facilities.Region = Region.RegionNo RIGHT OUTER JOIN ApprovedSMSCells ON 
                Facilities.FacCode = ApprovedSMSCells.FacCode WHERE ((ApprovedSMSCells.Deleted <> 1) OR
                (ApprovedSMSCells.Deleted IS NULL)) AND (Region.RegionNo = @RegionNo) 
                AND (Facilities.SupportingPartnerId = @SupportingPartnerId)";
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@RegionNo";
                    param.DbType = DbType.Int32;
                    param.Value = regionId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;
                    param = command.CreateParameter();
                    param.ParameterName = "@RegionNo";
                    param.DbType = DbType.Int32;
                    param.Value = regionId;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAllSMSUsersByRegionBySupportingPartner(regionId, supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetAllSMSUsers(int supportingPartnerId)
        {
            DataTable dataTable = null;
            string query = @"SELECT ApprovedSMSCells.ApprovedCellNo, ApprovedSMSCells.CellNum, 
                ApprovedSMSCells.Name, ApprovedSMSCells.LangID, ApprovedSMSCells.SendSMS, ApprovedSMSCells.FacCode, 
                ApprovedSMSCells.Email, Facilities.FacName, District.District, Region.RegionName 
                FROM District INNER JOIN Facilities ON District.DistrictNo = Facilities.District INNER JOIN
                Region ON Facilities.Region = Region.RegionNo RIGHT OUTER JOIN ApprovedSMSCells ON 
                Facilities.FacCode = ApprovedSMSCells.FacCode 
                WHERE (Facilities.SupportingPartnerId = @SupportingPartnerId) AND ((ApprovedSMSCells.Deleted <> 1) 
                OR (ApprovedSMSCells.Deleted IS NULL))";
            DbCommand command = null;
            DbParameter param = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = query;

                    param = command.CreateParameter();
                    param.ParameterName = "@SupportingPartnerId";
                    param.DbType = DbType.Int32;
                    param.Value = supportingPartnerId;
                    command.Parameters.Add(param);
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAllSMSUsersBySupportingPartner(supportingPartnerId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }
        #endregion

        #region DHIS Link
        public static ReportDataSetLink GetReportDataSetLink(int reportId)
        {
            ReportDataSetLink reportDataSetLink = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = (from d in db.ReportDataSetLinks
                                           .Include("ReportDataSetElementsLinks")
                                           .Include("ReportDataSetElementsLinks.ReportIndicator")
                                       where d.ReportId == reportId
                                       select d).FirstOrDefault();
                        reportDataSetLink = results;
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new InvalidOperationException("MS Access databases are not supported.");

                case DataAccessMethod.WEB_SERVICES:
                    webService.AuthenticationHeaderValue = new IQServices.AuthenticationHeader();
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;

                    var output = webService.GetReportDataSetLink(reportId);
                    reportDataSetLink = Services.Deserialize(output, typeof(ReportDataSetLink)) as ReportDataSetLink;
                    break;
            }

            return reportDataSetLink;
        }

        public static bool SaveReportDataSetLink(ReportDataSetLink reportDataSetLink)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = (from d in db.ReportDataSetLinks.Include("ReportDataSetElementsLinks")
                                       where d.ReportId == reportDataSetLink.ReportId
                                       select d).FirstOrDefault();
                        if (results == null)
                        {
                            // New entry, it needs adding
                            db.ReportDataSetLinks.Add(reportDataSetLink);
                            db.SaveChanges();
                            return true;
                        }
                        else
                        {
                            // Remove dataElements records and put the new ones
                            results.ReportId = reportDataSetLink.ReportId;
                            results.DataSetId = reportDataSetLink.DataSetId;
                            results.ReportDataSetElementsLinks = reportDataSetLink.ReportDataSetElementsLinks;
                            db.SaveChanges();
                            return true;
                        }
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new InvalidOperationException("MS Access databases are not supported.");

                case DataAccessMethod.WEB_SERVICES:
                    var input = new IQServices.ReportDataSetLink
                    {
                        DataSetId = reportDataSetLink.DataSetId,
                        Id = reportDataSetLink.Id,
                        ReportId = reportDataSetLink.ReportId,
                    };

                    if (reportDataSetLink.ReportDataSetElementsLinks == null) input.ReportDataSetElementsLinks = null;
                    else
                    {
                        var elements = new List<IQServices.ReportDataSetElementsLink>();
                        foreach (var e in reportDataSetLink.ReportDataSetElementsLinks)
                        {
                            var element = new IQServices.ReportDataSetElementsLink
                            {
                                DataElementId = e.DataElementId,
                                ReportDataSetLinkId = e.ReportDataSetLinkId,
                                ReportIndicatorId = e.ReportIndicatorId
                            };
                            elements.Add(element);
                        }
                        input.ReportDataSetElementsLinks = elements.ToArray();
                    }
                    
                    webService.AuthenticationHeaderValue = new IQServices.AuthenticationHeader();
                    webService.AuthenticationHeaderValue.Username = Username;
                    webService.AuthenticationHeaderValue.PasswordHash = PasswordHash;
                    bool saved = webService.SaveReportDataSetLink(input);
                    return saved;
            }

            return false;
        }
        #endregion
    }
}
